<?php
// brand4S zsn 20170420 15:32
class AutoBrand4sModel extends RelationModel{
	
	protected $tableName = 'auto_brand_4s'; //重新定义表名
	
	//获取品牌下拉框列表
	public function getBrandList4sForSelect($uid){
		$sql = "select ab.id,ab.name from auto_brand ab,auto_brand_4s ab4
				where ab.id=ab4.bid and ab4.uid=$uid;";
		$model = new Model();
		return $model->query($sql);
	}
	//获取首页品牌TOP4 FOR INDEX
	public function getBrandBind4sTop4($uid){
		$sql="select ab.id,ab.name,count(1) num from product p,auto_brand ab,auto_brand_4s ab4,auto_obd_bind aob,auto_user_bind aub 
			where ab4.uid=$uid and p.uid=$uid and ab.id=ab4.bid and aob.obd_no=p.snid and aob.userid=aub.userid and aub.bid=ab.id 
			group by ab.id order by num desc limit 4;";
		$model = new Model();
		return $model->query($sql);
	}
	//获取4S店品牌及绑定车辆个数 by xd.5 201505 FOR INDEX
	public function getBrandBind4s($uid,$total){
		$sql="select ab.id,ab.name,count(1) num,round(count(1)/$total*100,1) per from product p,auto_brand ab,auto_brand_4s ab4,auto_obd_bind aob,auto_user_bind aub 
			where ab4.uid=$uid and p.uid=$uid and ab.id=ab4.bid and aob.obd_no=p.snid and aob.userid=aub.userid and aub.bid=ab.id 
			group by ab.id;";
		$model = new Model();
		return $model->query($sql);
	}
	
	//首页，获取近期需保养/近期已保养的统计数据，按品牌
	public function getBrandSumaryForIndex($queryType,$userInfo){
		$where = "";
		if($queryType=="need")
			$where = "and ((aum.next_by_mileage-aub.total_mileage)<=500 OR (aum.next_by_time - UNIX_TIMESTAMP(now())) <=(30*24*60*60))";
		else if($queryType=="has")
			$where = "and (((aum.mileage-aub.total_mileage)<=500 and (((aum.mileage-aub.total_mileage)>=0) OR (aum.datetime-(UNIX_TIMESTAMP(now())) <=(30*24*60*60)) and (aum.datetime-UNIX_TIMESTAMP(now())) >=0)";
		$sql = "select aub.bid id,count(1) num from auto_user au
		join auto_user_bind aub on au.id=aub.userid
		join auto_obd_bind aob on aob.userid=aub.userid
		join product p on p.snid=aob.obd_no
		left join (SELECT tmp.* FROM (select * from auto_user_maintenance a order by datetime desc) tmp  group by tmp.userid) aum on aum.userid=au.id
		where p.uid={$userInfo['id']} and p.delete_type=0 $where
		group by aub.bid;";
		$model = new Model();
		return $model->query($sql);
	}
	//获取4S店的品牌列表
	public function getBrandList4s($userInfo){
		$sql = "select ab.id,ab.name from auto_brand ab,auto_brand_4s ab4 where ab.id=ab4.bid and ab4.uid={$userInfo['id']};";
		$model = new Model();
		return $model->query($sql);
	}
	//根据终端设备绑定的品牌，自动维持4S店的品牌列表
	public function autoInsertBrand4s($userInfo){
		$sql1 = "select bid id from auto_brand_4s where uid={$userInfo['id']};";
		
		$sql2 = "select aub.bid id from auto_user_bind aub
	join auto_obd_bind aob on aob.userid=aub.userid
	join product p on p.snid=aob.obd_no
	where p.uid={$userInfo['id']} and p.delete_type=0
	group by aub.bid;";
		
		$model = new Model();
		$needList = $model->query($sql2);
		$hasList = $model->query($sql1);
		$hasIDS=",";
		foreach ($hasList as $has){
			$hasIDS .= $has['id'].",";
		}
		//echo $hasIDS;
		foreach($needList as $need){
			$pos = strpos($hasIDS, ",".$need['id'].",");
			if($pos === false){
				$data['bid']=$need['id'];
				$data['uid']=$userInfo['id'];
				$data['inputtime']=date('Y-m-d H:i:s',time());
				$b = $this->add($data);
				//dump($data);
			}else{
				$hasIDS = str_replace(",{$need['id']},", ",", $hasIDS);//排除交集，剩下的是需要删除的记录
			}
		}
		//删除多余品牌
		if(strlen($hasIDS)>=3){
			$hasIDS = "0{$hasIDS}0";
			$sqlDelete = "delete from auto_brand_4s where uid={$userInfo['id']} and bid in ({$hasIDS})";
			$rNum = $model->execute($sqlDelete);
//			echo $sqlDelete;
		}
//		else{
//			echo $hasIDS;
//		}
	}
	//获取对应品牌的车型列表
	public function getBrandModelList4s($brandid){
		$r = array();
		if($brandid!='all'){
			$sql = "select am.id,am.name,am.year from auto_model am
			where am.bid={$brandid} order by am.name asc;";
			$model = new Model();
			$r =  $model->query($sql);
		}
		
		return $r;
		
	}
}